package com.crm.dao.mapper.customMapper.returnMoneyManagerCustomMapper;

import com.crm.model.qo.returnMoney.BankFlowQO;
import com.crm.model.vo.returnMoney.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * @author yuanyunfeng
 * @date 2021/7/26 14:29
 */
@Repository
public interface BankFlowCustomMapper {

    @SelectProvider(type = SqlProvider.class, method = "selectProductOrderId")
    List<ProductOrderInfoVO> selectProductOrderId(@Param("companyId") String companyId, @Param("customerName") String customerName);


    @SelectProvider(type = SqlProvider.class, method = "queryBankList")
    List<BankFlowQueryVO> queryBankList(@Param("currentCompanyId") String currentCompanyId, BankFlowQO bankFlowQO);


    @Select("select return_money_day_num from  sys_parameter_setting limit 1")
    int getReturnMoneyDayNum();


    @SelectProvider(type = SqlProvider.class, method = "queryMonthMoney")
    BankFlowDataVO queryMonthMoney(@Param("createPeopleId") String createPeopleId, @Param("queryYear") String queryYear, @Param("queryMonth") String queryMonth);


    @SelectProvider(type = SqlProvider.class, method = "queryBankRelatedByFlowId")
    List<BankFlowRelateVO> queryBankRelatedByFlowId(@Param("flowId") String flowId);


    @SelectProvider(type = SqlProvider.class, method = "queryOperatorLog")
    List<BankFlowReturnLogVO> queryOperatorLog(@Param("flowId") String flowId, @Param("orderNumber") String orderNumber);


    @SelectProvider(type = SqlProvider.class, method = "queryReturnMoney")
    List<BankOrderReturn> queryReturnMoney(@Param("flowId") String flowId, @Param("orderNumber") String orderNumber);

    @SelectProvider(type = SqlProvider.class, method = "queryPayMoney")
    List<BankOrderReturn> queryPayMoney(@Param("flowId") String flowId, @Param("payNumber") String payNumber);


    @SelectProvider(type = SqlProvider.class, method = "queryCustomerPayLog")
    List<BankFlowReturnLogVO> queryCustomerPayLog(@Param("flowId") String flowId, @Param("payNumber") String payNumber);


    @SelectProvider(type = SqlProvider.class, method = "querySubAvailableAmount")
    List<BankFlowReturnLogVO> querySubAvailableAmount(@Param("flowId") String flowId);

    /**
     * 通过订单编号的公司，流水号查询流水可用余额
     *
     * @param serial
     * @param companyId
     * @return
     */
    BigDecimal selectCountAvailableAmount(@Param("serial") String serial, @Param("companyId") String companyId);



    class SqlProvider {
        public String selectProductOrderId(String companyId, String customerName) {
            SQL sql = new SQL().SELECT("o.product_order_id AS productOrderId,\n" +
                    "\to.customer_name,\n" +
                    "\to.order_number AS orderNumber \n" +
                    "FROM\n" +
                    "\tchs_customer_product_order o\n" +
                    "\tLEFT JOIN tb_user_account u ON u.account_id = o.create_people_id \n" +
                    "WHERE\n" +
                    "\t(o.order_status = '1' or o.order_status = '4') \n" +
                    "\tAND u.company_id = #{companyId} \n" +
                    "\tAND o.customer_name = #{customerName}");
            return sql.toString();
        }

        public String queryBankList(String currentCompanyId, BankFlowQO bankFlowQO) {
            SQL sql = new SQL();
            sql.SELECT("bf.flow_id AS flowId,\n" +
                    "\tbf.transaction_no AS transactionNo,\n" +
                    "\tbf.abstracts AS abstracts,\n" +
                    "\tbf.transaction_date AS transactionDate,\n" +
                    "\tbf.payee_name,\n" +
                    "CASE\n" +
                    "\t\tbf.payee_name \n" +
                    "\t\tWHEN '北京财税研究院' THEN\n" +
                    "\t\t'1' \n" +
                    "\twhen '大成方略网络科技有限公司' then '2'\n" +
                    "\twhen '北京大成方略财务顾问有限公司' then '3'\n" +
                    "\twhen '北京大成方略纳税人俱乐部有限公司' then '4'\n" +
                    "\twhen '大成方略纳税人俱乐部股份有限公司' then '5'\n" +
                    "\telse '0'\n" +
                    "\tend AS payeeName," +
                    "\tbf.payee_name oldPayeeName," +
                    "\tbf.opposite_company AS oppositeCompany,\n" +
                    "\tbf.credit_money AS creditMoney,\n" +
                    "\tbf.available_amount AS availableAmount,\n" +
                    "\tbf.used_amount AS usedAmount,\n" +
                    "\tbf.company_name AS companyName,\n" +
                    "\tbf.relation_state AS relationState,\n" +
                    "\tbf.opposite_bank AS oppositeBank,\n" +
                    "\tbf.opposite_bank_account AS oppositeBankAccount,\n" +
                    "\tbf.payee_acount AS payeeAcount\n" +
                    "FROM\n" +
                    "\tbank_flow bf ");
            if (StringUtils.isNotBlank(bankFlowQO.getTransactionNo())) {
                sql.WHERE("transaction_no = ('" + bankFlowQO.getTransactionNo() + "')");
            }
            if (StringUtils.isNotBlank(bankFlowQO.getQueryYear())) {
                sql.WHERE("DATE_FORMAT(transaction_date,'%Y') = '" + bankFlowQO.getQueryYear() + "'");
            }
            if (StringUtils.isNotBlank(bankFlowQO.getQueryMonth())) {
                sql.WHERE("DATE_FORMAT(transaction_date,'%m') = '" + bankFlowQO.getQueryMonth() + "'");
            }
            if (StringUtils.isNotBlank(bankFlowQO.getCustomStartDate())) {
                sql.WHERE("DATE_FORMAT( transaction_date, '%Y-%m-%d' ) >= '" + bankFlowQO.getCustomStartDate() + "'");
            }
            if (StringUtils.isNotBlank(bankFlowQO.getCustomEndDate())) {
                sql.WHERE("DATE_FORMAT( transaction_date, '%Y-%m-%d' ) <= '" + bankFlowQO.getCustomEndDate() + "'");
            }
            if (StringUtils.isNotBlank(currentCompanyId)) {
                sql.WHERE("company_id = #{currentCompanyId}");//当前用户只能查看当前用户的
            }
            if (StringUtils.isNotBlank(bankFlowQO.getCompanyId())) {
                sql.WHERE("company_id = '" + bankFlowQO.getCompanyId() + "'");
            }
            if (StringUtils.isNotBlank(bankFlowQO.getRelationState())) {
                sql.WHERE("relation_state = '" + bankFlowQO.getRelationState() + "'");
            }
            if (StringUtils.isNotBlank(bankFlowQO.getOppositeCompany())) {
                sql.WHERE("opposite_company like '%" + bankFlowQO.getOppositeCompany() + "%'");
            }
            sql.WHERE("bf.operate_flag ='ok' ");
            sql.ORDER_BY("create_time desc");
            return sql.toString();
        }


        public String queryMonthMoney(String createPeopleId, String queryYear, String queryMonth) {
            SQL sql = new SQL();
            sql.SELECT("IFNULL(sum( credit_money ),0) AS monthAmountMoney,\n" +
                    "\tIFNULL(sum( available_amount ),0) AS monthUnallocatedMoney  \n" +
                    "FROM bank_flow");
            if (StringUtils.isNotBlank(createPeopleId)) {
                sql.WHERE("create_people_id = #{createPeopleId}");
            }
            if (StringUtils.isNotBlank(queryYear)) {
                sql.WHERE("DATE_FORMAT(transaction_date,'%Y') = #{queryYear}");
            }
            if (StringUtils.isNotBlank(queryMonth)) {
                sql.WHERE("DATE_FORMAT(transaction_date,'%m') = #{queryMonth}");
            }
            return sql.toString();
        }


        public String queryBankRelatedByFlowId(String flowId) {
            SQL sql = new SQL();
            sql.SELECT("bo.order_id AS productOrderId,\n" +
                    "\tr.order_number AS orderNumber,\n" +
                    "\tr.return_money_id AS returnMoneyId,\n" +
                    "\tp.chs_customer_pay_id AS customerPayId,\n" +
                    "\tp.pay_number AS payNumber,\n" +
                    "\tr.product_name AS productName,\n" +
                    "\tr.finish_order_product_num AS finishOrderProductNum,\n" +
                    "\tifnull(r.arrive_money,p.pay_money) AS arriveMoney,\n" +
                    "\tifnull(r.arrive_time,p.payment_date) AS arriveTime,\n" +
                    "\tbo.relate_type AS relateType,\n" +
                    "\tr.create_by,\n" +
                    "\t( SELECT user_name FROM tb_user_account WHERE account_id = bo.create_people_id ) AS inputName,\n" +
                    "\tifnull(r.exam_state,p.exam_state) AS examState \n" +
                    "FROM\n" +
                    "\tbank_flow bf\n" +
                    "\tLEFT JOIN bank_order bo ON bf.flow_id = bo.flow_id\n" +
                    "\tLEFT JOIN chs_return_money r ON bo.return_money_id = r.return_money_id \n" +
                    "\tAND r.del_flag != '1'\n" +
                    "\tLEFT JOIN chs_customer_pay p ON bo.customer_pay_id = p.chs_customer_pay_id \n" +
                    "\tAND p.delete_status = '0' ");
            sql.WHERE("bo.del_flag='0' and bf.flow_id = #{flowId}");
            return sql.toString();
        }


        public String queryOperatorLog(String flowId, String orderNumber) {
            SQL sql = new SQL();
            sql.SELECT("r.arrive_num AS arriveNum,\n" +
                    "\tl.operator_type AS operatorTypeName,\n" +
                    "\tl.former_data AS formerData,\n" +
                    "\tl.new_data AS newData,\n" +
                    "\tl.create_tm AS createTm,\n" +
                    "\t( SELECT user_name FROM tb_user_account WHERE account_id = l.create_people_id ) AS createPeopleName \n" +
                    "FROM\n" +
                    "\tbank_flow bf\n" +
                    "\tLEFT join bank_order bo on bf.flow_id = bo.flow_id\n" +
                    "\tLEFT JOIN chs_return_money r ON bo.return_money_id = r.return_money_id\n" +
                    "\tLEFT JOIN chs_return_money_log l ON r.return_money_id = l.return_money_id");
            if (StringUtils.isNotBlank(flowId)) {
                sql.WHERE("bf.flow_id = #{flowId}");
            }
            if (StringUtils.isNotBlank(orderNumber)) {
                sql.WHERE("bo.order_number = #{orderNumber}");
            }
            sql.WHERE("bo.order_id = r.order_id AND bo.flow_id = l.flow_id");
            sql.ORDER_BY("l.create_tm desc");
            return sql.toString();
        }


        public String queryCustomerPayLog(String flowId, String payNumber) {
            SQL sql = new SQL();
            sql.SELECT("p.pay_number AS arriveNum,\n" +
                    "\tl.operator_type AS operatorTypeName,\n" +
                    "\tl.former_data AS formerData,\n" +
                    "\tl.new_data AS newData,\n" +
                    "\tl.create_tm AS createTm,\n" +
                    "\t( SELECT user_name FROM tb_user_account WHERE account_id = l.create_people_id ) AS createPeopleName \n" +
                    "FROM\n" +
                    "\tbank_flow bf\n" +
                    "\tLEFT JOIN bank_order bo ON bf.flow_id = bo.flow_id\n" +
                    "\tLEFT JOIN chs_customer_pay p ON bo.customer_pay_id = p.chs_customer_pay_id \n" +
                    "\tLEFT JOIN chs_return_money_log l ON bo.customer_pay_id = l.customer_pay_id ");
            if (StringUtils.isNotBlank(flowId)) {
                sql.WHERE("bf.flow_id = #{flowId}");
            }
            if (StringUtils.isNotBlank(payNumber)) {
                sql.WHERE("p.pay_number = #{payNumber}");
            }
            sql.WHERE("bo.flow_id = l.flow_id");
            sql.ORDER_BY("l.create_tm desc");
            return sql.toString();
        }


        public String querySubAvailableAmount(String flowId) {
            SQL sql = new SQL();
            sql.SELECT("l.modify_reason AS arriveNum,\n" +
                    "\tl.operator_type AS operatorTypeName,\n" +
                    "\tl.former_data AS formerData,\n" +
                    "\tl.new_data AS newData,\n" +
                    "\tl.create_tm AS createTm,\n" +
                    "\t( SELECT user_name FROM tb_user_account WHERE account_id = l.create_people_id ) AS createPeopleName \n" +
                    "FROM\n" +
                    "\tbank_flow bf\n" +
                    "\tLEFT JOIN chs_return_money_log l ON bf.flow_id = l.flow_id \n" +
                    "WHERE\n" +
                    "\tbf.flow_id = #{flowId} \n" +
                    "\tAND l.operator_type = '5' ");
            sql.ORDER_BY("l.create_tm desc");
            return sql.toString();
        }


        public String queryReturnMoney(String flowId, String orderNumber) {
            SQL sql = new SQL();
            sql.SELECT("bf.transaction_no AS transacationNo,\n" +
                    "\tr.order_id AS orderId,\n" +
                    "\tr.order_number AS orderNumber,\n" +
                    "\tr.product_name AS productName,\n" +
                    "\tr.exam_state AS examState,\n" +
                    "\tr.finish_order_product_num AS finishOrderProductNum,\n" +
                    "\tr.create_tm AS createTm,\n" +
                    "\tbf.opposite_company AS oppositeCompany,\n" +
                    "\tbf.opposite_bank_account oppositeBankAccount,\n" +
                    "\tr.arrive_time AS arriveTime,\n" +
                    "\tr.arrive_money AS arriveMoney,\n" +
                    "\tbf.payee_name AS payeeName,\n" +
                    "\t( SELECT user_name FROM tb_user_account WHERE account_id = r.create_by ) AS createPeople \n" +
                    "FROM\n" +
                    "\tbank_flow bf\n" +
                    "\tLEFT JOIN bank_order bo on bf.flow_id = bo.flow_id\n" +
                    "\tLEFT JOIN chs_return_money r ON bo.return_money_id = r.return_money_id ");
            if (StringUtils.isNotBlank(flowId)) {
                sql.WHERE("bf.flow_id = #{flowId}");
            }
            if (StringUtils.isNotBlank(orderNumber)) {
                sql.WHERE("bo.order_number = #{orderNumber}");
            }
            sql.WHERE("r.del_flag != '1' ");
            return sql.toString();
        }


        public String queryPayMoney(String flowId, String payNumber) {
            SQL sql = new SQL();
            sql.SELECT("bf.transaction_no AS transacationNo,\n" +
                    "\tp.chs_customer_pay_id AS orderId,\n" +
                    "\tp.pay_number AS orderNumber,\n" +
                    "\t'' AS productName,\n" +
                    "\tp.exam_state AS examState,\n" +
                    "\t'' AS finishOrderProductNum,\n" +
                    "\tp.create_tm AS createTm,\n" +
                    "\tbf.opposite_company AS oppositeCompany,\n" +
                    "\tbf.opposite_bank_account oppositeBankAccount,\n" +
                    "\tp.payment_date AS arriveTime,\n" +
                    "\tp.pay_money AS arriveMoney,\n" +
                    "\tbf.payee_name AS payeeName,\n" +
                    "\t( SELECT user_name FROM tb_user_account WHERE account_id = p.create_people_id ) AS createPeople \n" +
                    "FROM\n" +
                    "\tbank_flow bf\n" +
                    "\tLEFT JOIN bank_order bo ON bf.flow_id = bo.flow_id\n" +
                    "\tLEFT JOIN chs_customer_pay p ON bo.customer_pay_id = p.chs_customer_pay_id");
            if (StringUtils.isNotBlank(flowId)) {
                sql.WHERE("bf.flow_id = #{flowId}");
            }
            if (StringUtils.isNotBlank(payNumber)) {
                sql.WHERE("p.pay_number = #{payNumber}");
            }
            sql.WHERE("p.delete_status = '0' ");
            return sql.toString();
        }


    }

}
